关于 Oracle Schema 级别的权限问题 |
您所在的位置：网站首页 › oracle 查看权限 › 关于 Oracle Schema 级别的权限问题 |
关于 Oracle Schema 级别的权限问题
|
作者 | JiekeXu 来源 |公众号 JiekeXu DBA之路（ID: JiekeXu_IT） 如需转载请联系授权 | (个人微信 ID：JiekeXu_DBA) 大家好，我是 JiekeXu,很高兴又和大家见面了,今天和大家一起来看看关于 Oracle Schema 级别的权限问题，欢迎点击上方蓝字“JiekeXu DBA之路”关注我的公众号，标星或置顶，更多干货第一时间到达！ 众所周知，在 Oracle 中，一般情况下 B 用户如果需要访问 A 用户下的表、视图、索引等对象，必须要加前缀即 A.object_name 才有权限访问。所以在生产环境中，一般也是 A 用户为业务用户，具有增删改查等业务相关的权限，B 用户一般给予查询 A 用户下表等对象的权限，所以我们可以加一层 Role 角色的权限（我们暂定为 R_SELECT_A），给 B 用户一个特定的角色 R_SELECT_A,然后定期定时通过脚本化的方式对这个角色授权。我们可以这么做： 创建业务用户和业务表空间，赋予相应的权限 Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.15.0.0.0 CREATE TABLESPACE A_DATA DATAFILE '+DATA' SIZE 30G UNIFORM SIZE 1M; CREATE USER A IDENTIFIED BY oracle DEFAULT TABLESPACE A_DATA QUOTA unlimited ON A_DATA TEMPORARY TABLESPACE temp; --授权 GRANT "CONNECT" TO "A"; GRANT "RESOURCE" TO "A"; GRANT "PLUSTRACE" TO "A"; GRANT DEBUG CONNECT SESSION TO "A"; GRANT CREATE SEQUENCE TO "A"; GRANT CREATE VIEW TO "A"; GRANT CREATE SYNONYM TO "A";创建 B 用户及角色 create user B identified by oracle; create role R_SELECT_A; grant connect,R_SELECT_A to B; --然后我们登录 A 用户，将 A 用户下的表对象只读权限给与角色 R_SELECT_A， --将执行结果再次执行一遍。 SQL> select 'grant select on '||table_name||' to r_select_A;' from user_tables where rownum> ${logfile} username=$1 password=$2 sqlplus -S /nolog >> ${logfile} > ${logfile} } ############################################################ # Main # ############################################################ case "${task}" in "user_a" ) grantToRole user_a passwd_oracle ;; "user_b" ) grantToRole user_b passwd_oracle ;; "user_c" ) grantToRole user_c passwd_oracle ;; "all" ) grantToRole user_a passwd_oracle grantToRole user_b passwd_oracle grantToRole user_c passwd_oracle ;; esac -- rm -f ${basedir}/grantToRole_*.sql -- crontab -- 0 14 * * * /home/oracle/tmp/grantToRole.sh all通过此脚本可以对单个用户或者所有用户赋权，注意我这里包括查询表及序列以及增删改的权限都有，可按照个人实际情况赋权。 在 Oracle 23c 中的解决办法当然这样还是稍微有点麻烦，对于新增的表要再次单独授权,不是很方便，于是乎在上个月 Oracle 发布的 23c 中，有一个新特性就是 schema 级别授权，什么意思呢？就是你可以给 B 用户授予查询 A 用户 schema 级别的权限，不再需要单个表、单个对象得授权，对于 23c 的环境你 只需三步快速体验 Oracle 23c 开发版。 grant select any table on schema USER_A to USER_B; 示例如下：让我们一起来看看。 SQL*Plus: Release 23.0.0.0.0 - Developer-Release on Wed May 31 15:55:43 2023Version 23.2.0.0.0Copyright (c) 1982, 2023, Oracle. All rights reserved.Connected to:Oracle Database 23c Free, Release 23.0.0.0.0 - Developer-ReleaseVersion 23.2.0.0.0--创建业务用户并授权CREATE TABLESPACE JIEKEXU_DATA DATAFILE '/opt/oracle/oradata/FREE/FREEPDB1/jiekexu_data01.dbf' SIZE 5G UNIFORM SIZE 1M;CREATE USER jiekexu IDENTIFIED BY Oracle_21c DEFAULT TABLESPACE JIEKEXU_DATA QUOTA unlimited ON JIEKEXU_DATATEMPORARY TABLESPACE TEMP;GRANT "CONNECT","RESOURCE" to jiekexu;--创建查询用户，给予登录权限create user jiekexu_sel IDENTIFIED BY jiekexu_sel; grant create session to jiekexu_sel; set line 240 col profile for a20set pages 999 col username for a25 col ACCOUNT_STATUS for a18 select USERNAME,ACCOUNT_STATUS,CREATED,PROFILE,PASSWORD_VERSIONS,DEFAULT_TABLESPACE from dba_users where account_status='OPEN' order by CREATED asc; 接下来我们登录到业务用户 JIEKEXU，但由于自从 21c 开始强制创建容器数据库，则是 PDB 环境，我们登录 PDB 下需要配置 tns 别名进行登录。 cd $ORACLE_HOME/network/admin [oracle@jiekexu admin]$ more tnsnames.ora # tnsnames.ora Network Configuration File: /opt/oracle/product/23c/dbhomeFree/network/admin/tnsnames.ora # Generated by Oracle configuration tools. FREE = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = jiekexu)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREE) ) ) LISTENER_FREE = (ADDRESS = (PROTOCOL = TCP)(HOST = jiekexu)(PORT = 1521)) --写入如下信息 [oracle@jiekexu admin]$ vim tnsnames.ora FREEPDB1 = (DESCRIPTION = (ADDRESS = (PROTOCOL = TCP)(HOST = jiekexu)(PORT = 1521)) (CONNECT_DATA = (SERVER = DEDICATED) (SERVICE_NAME = FREEPDB1) ) )然后我们登录进入业务用户创建表并插入数据。 conn jiekexu/Oracle_21c@FREEPDB1 create table test(id int,name varchar2(20)); insert into test values(1,'jiekexu'),(2,'freepdb'); --注意：如上的新特性，在 values 后面可以跟多个值了，以前 21c 之前只能跟一个。 commit;接下来我们用 SYS 授权 JIEKEXU_SEL 用户 schema 级别的查询权限。然后我们连接查询用户则是可以查到业务用户下的表的数据。 SQL> alter session set container=FREEPDB1; Session altered. SQL> grant select any table on schema jiekexu to jiekexu_sel; Grant succeeded. SQL> conn jiekexu_sel/jiekexu_sel@FREEPDB1 Connected. SQL> select * from jiekexu.test; ID NAME ---------- -------------------- 1 jiekexu 2 freepdb接下来我们模拟业务新增情况，在业务用户下新建表，再去查询用户下看是否可以查询到数据。 SQL> create table t_new(id number); SQL> insert into t_new values(1),(2),(3),(4); 4 rows created. SQL> commit; --切到查询用户，则可以查询到新建的表 t_new 表的数据。 SQL> conn jiekexu_sel/jiekexu_sel@FREEPDB1 Connected. SQL> select * from jiekexu.test; ID NAME ---------- -------------------- 1 jiekexu 2 freepdb SQL> select * from jiekexu.t_new; ID ---------- 1 2 3 4 SQL> show user; USER is "JIEKEXU_SEL" SQL> col OWNER for a15 SQL> col TABLE_NAME for a10 SQL> select owner,table_name from all_tables where owner not in ('SYS','MDSYS','SYSTEM','XDB','CTXSYS'); OWNER TABLE_NAME --------------- ---------- JIEKEXU TEST JIEKEXU T_NEW
授权 “select any table on schema” 后，JIEKEXU_SEL 用户除了原先授予的 “CREAT SESSION” 的系统权限外，没有被授予任何其他系统权限和系统角色。 根据上月初 2023 嘉年华上杨长老的演讲介绍，SCHEMA 级授权是通过对象级的继承权限获得，此时，被授权的用户可以看到原始授权用户下所有的表以及数据。授权用户通过 ora_check_sys_privilege 函数获取当前是否具备访问权限，即若返回判断结果为 1，则用户有查看权限，返回结果为 0 则无权限。 SQL> select * from dba_role_privs where grantee='JIEKEXU_SEL'; no rows selected SQL> select * from dba_sys_privs where grantee='JIEKEXU_SEL'; GRANTEE PRIVILEGE ADM COM INH ----------- ---------------------------------------- --- --- --- JIEKEXU_SEL CREATE SESSION NO NO NO SQL> conn / as sysdba SQL> set long 99999 pages 9999 LONGCHUNKSIZE 99999 SQL> select dbms_metadata.get_ddl('VIEW','ALL_TABLES','SYS') from dual; 来了一个新需求现在又遇到一个新的需求，怎么个情况呢，且听我慢慢道来，新来一业务要求可以仅读取 PROD 业务用户下的表，但是所有程序 JDBC 里配置的都是 READONLY 用户，但是他的程序代码里 SQL 写的全都没有加前缀，类似于“select count(*) from test" 这样访问数据库。这样就 100% 报错了，因为 READONLY 用户是没有 test 表的，业务方又不想改代码中涉及到的表加前缀，那么只能通过创建一个触发器，当登录到 READONLY 用户时则将会立马切到业务用户 PROD，然后再给 READONLY 用户查询业务用户 PROD 的角色 R_SELECT_PROD，则就可以实现登录到 READONLY 用户也不用写前缀便可以查询到 PROD 业务用户数据了。 $ sqlplus / as sysdba SQL*Plus: Release 19.0.0.0.0 - Production on Wed May 31 16:13:59 2023 Version 19.15.0.0.0 Copyright (c) 1982, 2022, Oracle. All rights reserved. Connected to: Oracle Database 19c Enterprise Edition Release 19.0.0.0.0 - Production Version 19.15.0.0.0 SQL> create user READONLY identified by READONLY; SQL> grant connect,R_SELECT_PROD to READONLY; SQL> create or replace trigger tri_default_schema_prod after logon on READONLY.schema begin execute immediate 'alter session set current_schema=PROD'; end; / --登录只读用户查询业务用户下的表数据 CONN READONLY/READONLY SELECT COUNT(*) FROM PROD.TEST; SELECT COUNT(*) FROM TEST; --均是访问 PROD.TEST 的表我的临时解决方案是这样的，不知小伙伴们还有其他的方法吗？欢迎添加我微信一起交流讨论。另外:近期新建一个微信交流群，现 150 多人了，最终需要 200 人左右，也算比较活跃，会在群中不定期举行抽奖、红包福利，限时免费开放名额，如有需要的可添加我个人微信【JiekeXu_DBA】，备注：加群。 全文完，希望可以帮到正在阅读的你，如果觉得此文对你有帮助，可以分享给你身边的朋友，同事，你关心谁就分享给谁，一起学习共同进步~~~ 欢迎关注我的公众号【JiekeXu DBA之路】，第一时间一起学习新知识！ ———————————————————————————公众号：JiekeXu DBA之路CSDN ：https://blog.csdn.net/JiekeXu墨天轮：https://www.modb.pro/u/4347腾讯云：https://cloud.tencent.com/developer/user/5645107———————————————————————————
Linux 环境搭建 MySQL8.0.28 主从同步环境
|
【本文地址】
今日新闻 |
推荐新闻 |